/****** Object:  StoredProcedure [dbo].[sp_GetNsToChuc]    Script Date: 08/25/2011 23:17:47 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_GetNsToChuc]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_GetNsToChuc]
GO

/****** Object:  StoredProcedure [dbo].[sp_GetNsToChuc]    Script Date: 08/25/2011 23:17:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_GetNsToChuc]
	@ToChucID int,
 @Debug int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	SET NOCOUNT ON;
 IF @ToChucID IS NULL 
 BEGIN
  RAISERROR ('Ban phai nhap NSToChucID',16,1)
  RETURN
 END

  SELECT 
   ToChucID as int,--tc.ToChucID,
   CONVERT(int, NULL) as ToChucID,
   tc.TenToChucShort,
   tc.TenToChucFull,
   tc.ToChucIDCha,
   tc.ToChucLevel,
   tc.XepHangDonViID,

   Convert(int,NULL) as Nu, --K
   Convert(int,NULL) as DangVien,--L
   Convert(int,NULL) as DanTocItNguoi,--M
   Convert(int,NULL) as Duoi30Tuoi,--N
   Convert(int,NULL) as Tuoi30_50,--O
   Convert(int,NULL) as Tuoi51_60,--P
   Convert(int,NULL) as Nu_54Tuoi,--Q
   Convert(int,NULL) as Nam_59Tuoi,--R

   Convert(int,NULL) as LLCT_CaoCap,--T
   Convert(int,NULL) as LLCT_TrungCap,--U
   Convert(int,NULL) as LLCT_SoCap,--V
   Convert(int,NULL) as AnhVan_CuNhan,--W
   Convert(int,NULL) as AnhVan_CoSo,--X
   Convert(int,NULL) as NNKhac_CuNhan,--Y
   Convert(int,NULL) as NNKhac_CoSo,--Z
   Convert(int,NULL) as TinHoc_CuNhan,--AA
   Convert(int,NULL) as TinHoc_CoSo,--AB

   Convert(int,NULL) as ThayThuocNhanDan,--AC
   Convert(int,NULL) as ThayThuocUuTu,--AD
   Convert(int,NULL) as NhaGiaoNhanDan,--AE
   Convert(int,NULL) as NhaGiaoUuTu,--AF

   Convert(int,NULL) as TongSoBacSi,--AS
   Convert(int,NULL) as TongSoBacSi_Nu,--AT
   Convert(int,NULL) as GiaoSuY,--AU
   Convert(int,NULL) as GiaoSuY_Nu,--AV
   Convert(int,NULL) as PhoGiaoSuY,--AW
   Convert(int,NULL) as PhoGiaoSuY_Nu,--AX
   Convert(int,NULL) as TienSiY,--AY
   Convert(int,NULL) as TienSiY_Nu,--AZ
   Convert(int,NULL) as ThacSiY,--BA
   Convert(int,NULL) as ThacSiY_Nu,--BB
   Convert(int,NULL) as BacSiCK2,--BC
   Convert(int,NULL) as BacSiCK2_Nu,--BD
   Convert(int,NULL) as BacSiCK1,--BE
   Convert(int,NULL) as BacSiCK1_Nu,--BF
   Convert(int,NULL) as BacSiNha,--BG
   Convert(int,NULL) as BacSiNha_Nu,--BH
   Convert(int,NULL) as NgachBacSi_ChuyenVienCaoCap,--BK
   Convert(int,NULL) as NgachBacSi_ChuyenVienChinh,--BL
   Convert(int,NULL) as NgachBacSi_ChuyenVien,--BM
   Convert(int,NULL) as NgachBacSiCaoCap,--BN
   Convert(int,NULL) as NgachBacSiChinh,--BO
   Convert(int,NULL) as NgachBacSi,--BP
   
   Convert(int,NULL) as DuocSiDaiHoc,--BR
   Convert(int,NULL) as DuocSiDaiHoc_Nu,--BS
   Convert(int,NULL) as GiaoSuDuoc,--BT
   Convert(int,NULL) as GiaoSuDuoc_Nu,--BU
   Convert(int,NULL) as PhoGiaoSuDuoc,--BV
   Convert(int,NULL) as PhoGiaoSuDuoc_Nu,--BW
   Convert(int,NULL) as TienSiDuoc,--BX
   Convert(int,NULL) as TienSiDuoc_Nu,--BY
   Convert(int,NULL) as ThacSiDuoc,--BZ
   Convert(int,NULL) as ThacSiDuoc_Nu,--CA
   Convert(int,NULL) as DuocSiCK2,--CB
   Convert(int,NULL) as DuocSiCK2_Nu,--CC
   Convert(int,NULL) as DuocSiCK1,--CD
   Convert(int,NULL) as DuocSiCK1_Nu,--CE
   Convert(int,NULL) as NgachDuocSi_ChuyenVienCaoCap,--CH
   Convert(int,NULL) as NgachDuocSi_ChuyenVienChinh,--CI
   Convert(int,NULL) as NgachDuocSi_ChuyenVien,--CJ
   Convert(int,NULL) as NgachDuocSiCaoCap,--CK
   Convert(int,NULL) as NgachDuocSiChinh,--CL
   Convert(int,NULL) as NgachDuocSi,--CM
   
   Convert(int,NULL) as ThacSiYTeCongCong,--CO
   Convert(int,NULL) as ThacSiYTeCongCong_Nu,--CP
   Convert(int,NULL) as DaiHocYTeCongCong,--CQ
   Convert(int,NULL) as DaiHocYTeCongCong_Nu,--CR
   Convert(int,NULL) as NgachYTeCongCongChinh,--CS
   Convert(int,NULL) as NgachYTeCongCong,--CT
   
   Convert(int,NULL) as YSi,--CU
   Convert(int,NULL) as YSi_Nu,--CV
   Convert(int,NULL) as NgachYSi,--CW
   Convert(int,NULL) as NgachYSiCanSu,--CX
   
   Convert(int,NULL) as KTVNhaKhoa,--CZ
   Convert(int,NULL) as KTVNhaKhoa_Nu,--DA
   Convert(int,NULL) as NgachKTVNhaKhoa,--DB
   
   Convert(int,NULL) as KTVY,--DC
   Convert(int,NULL) as KTVY_Nu,--DD
   Convert(int,NULL) as KTVYDaiHoc,--DE
   Convert(int,NULL) as KTVYCaoDang,--DF
   Convert(int,NULL) as KTVYTrungHoc,--DG
   Convert(int,NULL) as KTVYSoHoc,--DH
   Convert(int,NULL) as NgachKTVYChinh,--DI
   Convert(int,NULL) as NgachKTVY,--DJ
   Convert(int,NULL) as NgachKTVYCaoDang,--DK
   Convert(int,NULL) as NgachKTVYTrungHoc,--DL
   Convert(int,NULL) as NgachKTVYSoHoc,--DL
   
   Convert(int,NULL) as DuocSiTH_KTVTrungHocDuoc,--DM
   Convert(int,NULL) as DuocSiTH_KTVTrungHocDuoc_Nu,--DN
   Convert(int,NULL) as NgachDuocSiTH,--DO
   
   Convert(int,NULL) as DieuDuong,--DQ
   Convert(int,NULL) as DieuDuong_Nu,--DR
   Convert(int,NULL) as DieuDuongDaiHoc,--DS
   Convert(int,NULL) as DieuDuongCaoDang,--DT
   Convert(int,NULL) as DieuDuongTrungHoc,--DU
   Convert(int,NULL) as DieuDuongSoHoc,--DV
   Convert(int,NULL) as NgachDieuDuongChinh,--DW
   Convert(int,NULL) as NgachDieuDuong,--DX
   Convert(int,NULL) as NgachDieuDuongCaoDang,--DY
   Convert(int,NULL) as NgachDieuDuongTrungCap,--DZ
   Convert(int,NULL) as NgachDieuDuongSoCap,--EA
   
   Convert(int,NULL) as HoSinh,--EB
   Convert(int,NULL) as HoSinh_Nu,--EC
   Convert(int,NULL) as HoSinhDaiHoc,--ED
   Convert(int,NULL) as HoSinhCaoDang,--EE
   Convert(int,NULL) as HoSinhTrungHoc,--EF
   Convert(int,NULL) as HoSinhSoHoc,--EG
   Convert(int,NULL) as NgachHoSinhChinh,--EH
   Convert(int,NULL) as NgachHoSinh,--EI
   Convert(int,NULL) as NgachHoSinhCaoDang,--EJ
   Convert(int,NULL) as NgachHoSinhTrungCap,--EK
   Convert(int,NULL) as NgachHoSinhSoCap,--EL
   
   Convert(int,NULL) as LuongY,--EM
   Convert(int,NULL) as LuongY_Nu,--EN
   Convert(int,NULL) as DuocTa,--EO
   Convert(int,NULL) as DuocTa_Nu,--EP
   Convert(int,NULL) as NgachDuocTa,--EQ
   
   Convert(int,NULL) as TDCMSauDaiHoc,--ES
   Convert(int,NULL) as TDCMSauDaiHoc_Nu,--ET
   Convert(int,NULL) as TDCMDaiHoc,--EU
   Convert(int,NULL) as TDCMDaiHoc_Nu,--EV
   Convert(int,NULL) as TDCMCaoDang,--EW
   Convert(int,NULL) as TDCMCaoDang_Nu,--EX
   Convert(int,NULL) as TDCMTrungHoc,--EY
   Convert(int,NULL) as TDCMTrungHoc_Nu,--EZ
   
   Convert(int,NULL) as NgachCBKhacChuyenVienCaoCap,--FC
   Convert(int,NULL) as NgachCBKhacChuyenVienChinh,--FD
   Convert(int,NULL) as NgachCBKhacChuyenVien,--FE
   Convert(int,NULL) as NgachCBKhacCanSu,--FF
   Convert(int,NULL) as NgachCBKhacNhanVien--FG
   
 INTO #TempNSToChuc
 FROM dbo.NSToChuc tc WITH (NOLOCK)
	  --dbo.NSNhanVien nv WITH (NOLOCK)
 WHERE 1=2

 CREATE INDEX #ToChucID ON #TempNSToChuc(ToChucID)

 declare @sqlString as nvarchar(max)
 declare @sqlStringWHR as nvarchar(max)
 declare @sqlParameters as nvarchar(max)
 declare @count as int
 
INSERT INTO #TempNSToChuc
SELECT 
  tc.ToChucID as ToChucID,
  tc.TenToChucShort,
  tc.TenToChucFull,
  tc.ToChucIDCha,
  tc.ToChucLevel,
  tc.XepHangDonViID,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  0,0,0,0,0,0,0,0,0,0,
  
  0,0,0,0,0,0,0,0,0,0
  --0,0,0,0,0,0,0,0,0,0

FROM dbo.NSToChuc tc WITH (NOLOCK)
WHERE tc.ToChucID = @ToChucID
if @Debug = 1 print 'insert success'
--Tao table tam chua danh sach NhanVien cua ToChuc, sau do chi truy van vao table nay
 SELECT  
      Convert(int,null) as NhanVienID
      ,nv.[HoTenKhaiSinh]
      ,nv.[HoTenKhac]
      ,nv.[NgaySinh]
      ,nv.[DiaChiNoiSinh]
      ,nv.[DiachiIDQueQuan]
      ,nv.[GioiTinhID]
      ,nv.[DanTocID]
      ,nv.[TonGiaoID]
      ,nv.[SoCMND]
      ,nv.[NgayCap]
      ,nv.[NoiCap]
      ,nv.[DiaChiIDHoKhau]
      ,nv.[DiaChiIDNoiOHienTai]     
      ,nv.[ToChucIDTuyenDung]
      ,nv.[NgayTuyenDung]      
      ,nv.[TrinhDoGiaoDucPhoThongID]
      ,nv.[HeGiaoDucPhoThongID]
      ,nv.[TrinhDoChuyenMonCaoNhatID]
      ,nv.[LyLuanChinhTriID]
      ,nv.[QuanLyNhaNuocID]
      ,nv.[TrinhDoTinHocID]
      ,nv.[ThongTinTrinhDoChuyenMon]
      ,nv.[SoTruongCongTac]
      ,nv.[TinhTrangSucKhoe]
      ,nv.[ChieuCao]
      ,nv.[CanNang]
      ,nv.[NhomMauID]
      ,nv.[NgayVaoDangCSVN]
      ,nv.[NgayChinhThucVaoDangCSVN]
      ,nv.[ToChucChinhTriThamGia]
      ,nv.[NgayNhapNgu]
      ,nv.[NgayXuatNgu]
      ,nv.[QuanHamCaoNhatID]
      ,nv.[DanhHieuCaoNhatID]
      ,nv.[HangThuongBinhID]
      ,nv.[ConGiaDinhChinhSachID]
      ,nv.[KiLuat]
      ,nv.[QuaTrinhDongBHXH]
      ,nv.[QuaTrinhDongBHYT]
      ,nv.[SoNgayNghiPhepConLai]
      ,nv.[LichSuChinhTri]
      ,nv.[QuanHeToChucNuocNgoai]
      ,nv.[QuanHeNhanThanNuocNgoai]
      ,nv.[CoQuanQuanLiId]
      ,nv.[SoHieu]
      ,nv.[CoQuanSuDungId]
      ,nv.[LoaiBangCapID]
      ,nn.[MaNghe]
      ,nbl.[MaNgach]
      ,nng.[TenNgoaiNgu]
      ,nng.[TrinhDoNgoaiNguId]
INTO #TempNSNhanVien
FROM  dbo.NSNhanVien nv WITH (NOLOCK) 
	  LEFT JOIN dbo.NSNgheNghiep nn WITH (NOLOCK) 
	  ON (nv.[NhanVienID] = nn.[NhanVienId])
	  LEFT JOIN dbo.NSNgachBacLuong nbl WITH (NOLOCK)
	  ON (nv.[NhanVienID] = nbl.[NhanVienId])
	  LEFT JOIN dbo.NSNgoaiNgu nng WITH (NOLOCK)
	  ON (nv.[NhanVienID] = nng.[NhanVienId])	  	  
WHERE  (nbl.NgayHieuLuc = (SELECT MAX(NgayHieuLuc) FROM NSNgachBacLuong WHERE NSNgachBacLuong.NhanVienID = nv.NhanVienID)
		OR nbl.NgayHieuLuc IS NULL)
  AND  (nn.ThoiGianThayDoi = (SELECT MAX(ThoiGianThayDoi) FROM NSNgheNghiep WHERE NSNgheNghiep.NhanVienID = nv.NhanVienID)
		OR nn.ThoiGianThayDoi IS NULL)
  AND  1=2

CREATE INDEX #TempNhanVienID ON #TempNSNhanVien(NhanVienID)

INSERT INTO #TempNSNhanVien
SELECT 
      nv.NhanVienID as NhanVienID
      ,nv.[HoTenKhaiSinh]
      ,nv.[HoTenKhac]
      ,nv.[NgaySinh]
      ,nv.[DiaChiNoiSinh]
      ,nv.[DiachiIDQueQuan]
      ,nv.[GioiTinhID]
      ,nv.[DanTocID]
      ,nv.[TonGiaoID]
      ,nv.[SoCMND]
      ,nv.[NgayCap]
      ,nv.[NoiCap]
      ,nv.[DiaChiIDHoKhau]
      ,nv.[DiaChiIDNoiOHienTai]      
      ,nv.[ToChucIDTuyenDung]
      ,nv.[NgayTuyenDung]     
      ,nv.[TrinhDoGiaoDucPhoThongID]
      ,nv.[HeGiaoDucPhoThongID]
      ,nv.[TrinhDoChuyenMonCaoNhatID]
      ,nv.[LyLuanChinhTriID]
      ,nv.[QuanLyNhaNuocID]
      ,nv.[TrinhDoTinHocID]
      ,nv.[ThongTinTrinhDoChuyenMon]
      ,nv.[SoTruongCongTac]
      ,nv.[TinhTrangSucKhoe]
      ,nv.[ChieuCao]
      ,nv.[CanNang]
      ,nv.[NhomMauID]
      ,nv.[NgayVaoDangCSVN]
      ,nv.[NgayChinhThucVaoDangCSVN]
      ,nv.[ToChucChinhTriThamGia]
      ,nv.[NgayNhapNgu]
      ,nv.[NgayXuatNgu]
      ,nv.[QuanHamCaoNhatID]
      ,nv.[DanhHieuCaoNhatID]
      ,nv.[HangThuongBinhID]
      ,nv.[ConGiaDinhChinhSachID]
      ,nv.[KiLuat]
      ,nv.[QuaTrinhDongBHXH]
      ,nv.[QuaTrinhDongBHYT]
      ,nv.[SoNgayNghiPhepConLai]
      ,nv.[LichSuChinhTri]
      ,nv.[QuanHeToChucNuocNgoai]
      ,nv.[QuanHeNhanThanNuocNgoai]
      ,nv.[CoQuanQuanLiId]
      ,nv.[SoHieu]
      ,nv.[CoQuanSuDungId]
      ,nv.[LoaiBangCapID]
      ,nn.[MaNghe]
      ,nbl.[MaNgach]
      ,nng.[TenNgoaiNgu]
      ,nng.[TrinhDoNgoaiNguId] 
FROM  dbo.NSNhanVien nv WITH (NOLOCK) 
	  LEFT JOIN dbo.NSNgheNghiep nn WITH (NOLOCK) 
	  ON (nv.[NhanVienID] = nn.[NhanVienId])
	  LEFT JOIN dbo.NSNgachBacLuong nbl WITH (NOLOCK)
	  ON (nv.[NhanVienID] = nbl.[NhanVienId])
	  LEFT JOIN dbo.NSNgoaiNgu nng WITH (NOLOCK)
	  ON (nv.[NhanVienID] = nng.[NhanVienId])	  	  
WHERE  (nbl.NgayHieuLuc = (SELECT MAX(NgayHieuLuc) FROM NSNgachBacLuong WHERE NSNgachBacLuong.NhanVienID = nv.NhanVienID)
		OR nbl.NgayHieuLuc IS NULL)
  AND  (nn.ThoiGianThayDoi = (SELECT MAX(ThoiGianThayDoi) FROM NSNgheNghiep WHERE NSNgheNghiep.NhanVienID = nv.NhanVienID)
		OR nn.ThoiGianThayDoi IS NULL)
  AND  nv.CoQuanQuanLiId = @ToChucID
----------
--Update So Luong Nhan Vien Nu	
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE  GioiTinhID = 1540 )
Update #TempNSToChuc
SET Nu = @count
--Update So Luong Dang Vien
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE NgayVaoDangCSVN IS NOT NULL )
Update #TempNSToChuc
SET DangVien = @count
--Update DanToc It Nguoi
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanTocID <> 1512 )
Update #TempNSToChuc
SET DanTocItNguoi = @count
--Update Tuoi < 30
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) < 30))
Update #TempNSToChuc
SET Duoi30Tuoi = @count
--Update Tuoi 30 -50
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) > 30) AND (datepart(year,getdate())-datepart(year,NgaySinh) < 51))
Update #TempNSToChuc
SET Tuoi30_50 = @count
--Update  Tuoi 51 - 60
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) > 50) AND (datepart(year,getdate())-datepart(year,NgaySinh) < 61))
Update #TempNSToChuc
SET Tuoi51_60 = @count
--Update Nu 50
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) = 54))
Update #TempNSToChuc
SET Nu_54Tuoi = @count
--Update Name 59
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (datepart(year,getdate())-datepart(year,NgaySinh) = 59))
Update #TempNSToChuc
SET Nam_59Tuoi = @count
--LLCT_CaoCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LyLuanChinhTriID = 1500)
Update #TempNSToChuc
SET  LLCT_CaoCap= @count
--LLCT_TrungCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LyLuanChinhTriID = 1499)
Update #TempNSToChuc
SET  LLCT_TrungCap= @count
--LLCT_SoCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LyLuanChinhTriID = 1498)
Update #TempNSToChuc
SET  LLCT_SoCap= @count
--AnhVan_CuNhan
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(TenNgoaiNgu)) = 'Anh' AND TrinhDoNgoaiNguId = 1578)
Update #TempNSToChuc
SET  AnhVan_CuNhan= @count
--AnhVan_CoSo
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(TenNgoaiNgu)) = 'Anh' AND TrinhDoNgoaiNguId = 1579)
Update #TempNSToChuc
SET  AnhVan_CoSo= @count
--NNKhac_CuNhan
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(TenNgoaiNgu)) <> 'Anh' AND TrinhDoNgoaiNguId = 1578)
Update #TempNSToChuc
SET  NNKhac_CuNhan= @count
--NNKhac_CoSo
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(TenNgoaiNgu)) <> 'Anh' AND TrinhDoNgoaiNguId = 1579)
Update #TempNSToChuc
SET  NNKhac_CoSo= @count
--TinHoc_CuNhan
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE TrinhDoTinHocID = 1570)
Update #TempNSToChuc
SET  TinHoc_CuNhan= @count
--TinHoc_CoSo
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE TrinhDoTinHocID IN(1571,1572,1573,1574))
Update #TempNSToChuc
SET  TinHoc_CoSo= @count

--ThayThuocNhanDan
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanhHieuCaoNhatID =1448)
Update #TempNSToChuc
SET  ThayThuocNhanDan= @count
--ThayThuocUuTu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanhHieuCaoNhatID =1451)
Update #TempNSToChuc
SET  ThayThuocUuTu= @count
--NhaGiaoNhanDan
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanhHieuCaoNhatID =1447)
Update #TempNSToChuc
SET  NhaGiaoNhanDan= @count
--NhaGiaoUuTu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE DanhHieuCaoNhatID =1450)
Update #TempNSToChuc
SET  NhaGiaoUuTu= @count

--TongSoBacSi
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999')
Update #TempNSToChuc
SET  TongSoBacSi= @count
--TongSoBacSi_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND GioiTinhID = 1540 )
Update #TempNSToChuc
SET  TongSoBacSi_Nu= @count
--GiaoSuY
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1470 )
Update #TempNSToChuc
SET  GiaoSuY= @count
--GiaoSuY_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1470 AND GioiTinhID = 1540 )
Update #TempNSToChuc
SET  GiaoSuY_Nu= @count
--PhoGiaoSuY
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1471 )
Update #TempNSToChuc
SET  PhoGiaoSuY= @count
--PhoGiaoSuY_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1471 AND GioiTinhID = 1540 )
Update #TempNSToChuc
SET  PhoGiaoSuY_Nu= @count
--TienSiY
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon IN (1472,1473) )
Update #TempNSToChuc
SET  TienSiY= @count
--TienSiY_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon IN (1472,1473)  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  TienSiY_Nu= @count
--ThacSiY
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1478 )
Update #TempNSToChuc
SET  ThacSiY= @count
--ThacSiY_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1478  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  ThacSiY_Nu= @count
--BacSiCK2
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1474 )
Update #TempNSToChuc
SET  BacSiCK2= @count
--BacSiCK2_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1474 AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  BacSiCK2_Nu= @count
--BacSiCK1
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1475 )
Update #TempNSToChuc
SET  BacSiCK1= @count
--BacSiCK1_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9999' AND ThongTinTrinhDoChuyenMon = 1475 AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  BacSiCK1_Nu= @count
--BacSiNha
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9997')
Update #TempNSToChuc
SET  BacSiNha= @count
--BacSiNha_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9997' AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  BacSiNha_Nu= @count
--NgachBacSi_ChuyenVienCaoCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.001' AND LTRIM(RTRIM(MaNghe)) = '9999')
Update #TempNSToChuc
SET  NgachBacSi_ChuyenVienCaoCap= @count
--NgachBacSi_ChuyenVienChinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.002' AND LTRIM(RTRIM(MaNghe)) = '9999')
Update #TempNSToChuc
SET  NgachBacSi_ChuyenVienChinh= @count
--NgachBacSi_ChuyenVien
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.003' AND LTRIM(RTRIM(MaNghe)) = '9999')
Update #TempNSToChuc
SET  NgachBacSi_ChuyenVien= @count
--NgachBacSiCaoCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.116')
Update #TempNSToChuc
SET  NgachBacSiCaoCap= @count
--NgachBacSiChinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.116')
Update #TempNSToChuc
SET  NgachBacSiChinh= @count
--NgachBacSi
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.117')
Update #TempNSToChuc
SET  NgachBacSi= @count
   
--DuocSiDaiHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND LoaiBangCapID IN(1304,1305) )
Update #TempNSToChuc
SET  DuocSiDaiHoc= @count
--DuocSiDaiHoc_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND LoaiBangCapID IN(1304,1305) AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  DuocSiDaiHoc_Nu= @count
--GiaoSuDuoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1470 )
Update #TempNSToChuc
SET  GiaoSuDuoc= @count
--GiaoSuDuoc_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1470 AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  GiaoSuDuoc_Nu= @count
--PhoGiaoSuDuoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1471 )
Update #TempNSToChuc
SET  PhoGiaoSuDuoc= @count
--PhoGiaoSuDuoc_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1471  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  PhoGiaoSuDuoc_Nu= @count
--TienSiDuoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon IN (1472,1473) )
Update #TempNSToChuc
SET  TienSiDuoc= @count
--TienSiDuoc_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon IN (1472,1473)  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  TienSiDuoc_Nu= @count
--ThacSiDuoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1478 )
Update #TempNSToChuc
SET  ThacSiDuoc= @count
--ThacSiDuoc_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1478   AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  ThacSiDuoc_Nu= @count
--DuocSiCK2
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1476 )
Update #TempNSToChuc
SET  DuocSiCK2= @count
--DuocSiCK2_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1476  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  DuocSiCK2_Nu= @count
--DuocSiCK1
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1477 )
Update #TempNSToChuc
SET  DuocSiCK1= @count
--DuocSiCK1_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9998' AND ThongTinTrinhDoChuyenMon = 1477  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  DuocSiCK1_Nu= @count
--NgachDuocSi_ChuyenVienCaoCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.001' AND LTRIM(RTRIM(MaNghe)) = '9998')
Update #TempNSToChuc
SET  NgachDuocSi_ChuyenVienCaoCap= @count
--NgachDuocSi_ChuyenVienChinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.002' AND LTRIM(RTRIM(MaNghe)) = '9998')
Update #TempNSToChuc
SET  NgachDuocSi_ChuyenVienChinh= @count
--NgachDuocSi_ChuyenVien
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '1.003' AND LTRIM(RTRIM(MaNghe)) = '9998')
Update #TempNSToChuc
SET  NgachDuocSi_ChuyenVien= @count
--NgachDuocSiCaoCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.132')
Update #TempNSToChuc
SET  NgachDuocSiCaoCap= @count
--NgachDuocSiChinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.133')
Update #TempNSToChuc
SET  NgachDuocSiChinh= @count
--NgachDuocSi
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.134')
Update #TempNSToChuc
SET  NgachDuocSi= @count
   
--ThacSiYTeCongCong
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9996' AND ThongTinTrinhDoChuyenMon = 1478)
Update #TempNSToChuc
SET  ThacSiYTeCongCong= @count
--ThacSiYTeCongCong_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9996' AND ThongTinTrinhDoChuyenMon = 1478   AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  ThacSiYTeCongCong_Nu= @count
--DaiHocYTeCongCong
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9996' AND LoaiBangCapID = 1304)
Update #TempNSToChuc
SET  DaiHocYTeCongCong= @count
--DaiHocYTeCongCong_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9996' AND LoaiBangCapID = 1304  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  DaiHocYTeCongCong= @count
--NgachYTeCongCongChinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16a.197')
Update #TempNSToChuc
SET  NgachYTeCongCongChinh= @count
--NgachYTeCongCong
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16a.198')
Update #TempNSToChuc
SET  NgachYTeCongCong= @count
  
--YSi
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9995')
Update #TempNSToChuc
SET  YSi= @count
--YSi_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9995'  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  YSi_Nu= @count
--NgachYSi
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.119')
Update #TempNSToChuc
SET  NgachYSi= @count
--NgachYSiCanSu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9995' AND LTRIM(RTRIM(MaNgach)) = '1.004')
Update #TempNSToChuc
SET  NgachYSiCanSu= @count
   
--KTVNhaKhoa
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9989')
Update #TempNSToChuc
SET  KTVNhaKhoa= @count
--KTVNhaKhoa_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9989' AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  KTVNhaKhoa_Nu= @count
--NgachKTVNhaKhoa
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.999')
Update #TempNSToChuc
SET  NgachKTVNhaKhoa= @count
   
--KTVY
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994')
Update #TempNSToChuc
SET  KTVY= @count
--KTVY_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  KTVY_Nu= @count
--KTVYDaiHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND LoaiBangCapID = 1304)
Update #TempNSToChuc
SET  KTVYDaiHoc= @count
--KTVYCaoDang
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND LoaiBangCapID = 1303)
Update #TempNSToChuc
SET  KTVYCaoDang= @count
--KTVYTrungHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND LoaiBangCapID = 1302)
Update #TempNSToChuc
SET  KTVYTrungHoc= @count
--KTVYSoHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9994' AND LoaiBangCapID = 1301)
Update #TempNSToChuc
SET  KTVYSoHoc= @count
--NgachKTVYChinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.284')
Update #TempNSToChuc
SET  NgachKTVYChinh= @count
--NgachKTVY
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.285')
Update #TempNSToChuc
SET  NgachKTVY= @count
--NgachKTVYCaoDang
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.286')
Update #TempNSToChuc
SET  NgachKTVYCaoDang= @count
--NgachKTVYTrungHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.287')
Update #TempNSToChuc
SET  NgachKTVYTrungHoc= @count
--NgachKTVYSoHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.288')
Update #TempNSToChuc
SET  NgachKTVYSoHoc= @count

--DuocSiTH_KTVTrungHocDuoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (LTRIM(RTRIM(MaNgach)) = '16.135' OR LTRIM(RTRIM(MaNgach)) = '16.137'))
Update #TempNSToChuc
SET  DuocSiTH_KTVTrungHocDuoc= @count
--DuocSiTH_KTVTrungHocDuoc_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (LTRIM(RTRIM(MaNgach)) = '16.135' OR LTRIM(RTRIM(MaNgach)) = '16.137') AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  DuocSiTH_KTVTrungHocDuoc_Nu= @count
--NgachDuocSiTH
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE (LTRIM(RTRIM(MaNgach)) = '16.135' OR LTRIM(RTRIM(MaNgach)) = '16.137'))
Update #TempNSToChuc
SET  NgachDuocSiTH= @count
   
--DieuDuong
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' )
Update #TempNSToChuc
SET  DieuDuong= @count
--DieuDuong_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993'  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  DieuDuong_Nu= @count
--DieuDuongDaiHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' AND LoaiBangCapID = 1304)
Update #TempNSToChuc
SET  DieuDuongDaiHoc= @count
--DieuDuongCaoDang
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' AND LoaiBangCapID = 1303)
Update #TempNSToChuc
SET  DieuDuongCaoDang= @count
--DieuDuongTrungHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' AND LoaiBangCapID = 1302)
Update #TempNSToChuc
SET  DieuDuongTrungHoc= @count
--DieuDuongSoHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9993' AND LoaiBangCapID = 1301)
Update #TempNSToChuc
SET  DieuDuongSoHoc= @count
--NgachDieuDuongChinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16a.199')
Update #TempNSToChuc
SET  NgachDieuDuongChinh= @count
--NgachDieuDuong
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16b.120')
Update #TempNSToChuc
SET  NgachDieuDuong= @count
--NgachDieuDuongCaoDang
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16a.120')
Update #TempNSToChuc
SET  NgachDieuDuongCaoDang= @count
--NgachDieuDuongTrungCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.b121')
Update #TempNSToChuc
SET  NgachDieuDuongTrungCap= @count
--NgachDieuDuongSoCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16b.122')
Update #TempNSToChuc
SET  NgachDieuDuongSoCap= @count
   
--HoSinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' )
Update #TempNSToChuc
SET  HoSinh= @count
--HoSinh_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992'  AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  HoSinh_Nu= @count
--HoSinhDaiHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' AND LoaiBangCapID = 1304)
Update #TempNSToChuc
SET  HoSinhDaiHoc= @count
--HoSinhCaoDang
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' AND LoaiBangCapID = 1303)
Update #TempNSToChuc
SET  HoSinhCaoDang= @count
--HoSinhTrungHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' AND LoaiBangCapID = 1302)
Update #TempNSToChuc
SET  HoSinhTrungHoc= @count
--HoSinhSoHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9992' AND LoaiBangCapID = 1301)
Update #TempNSToChuc
SET  HoSinhSoHoc= @count
--NgachHoSinhChinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.294')
Update #TempNSToChuc
SET  NgachDieuDuongSoCap= @count
--NgachHoSinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.295')
Update #TempNSToChuc
SET  NgachHoSinh= @count
--NgachHoSinhCaoDang
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.296')
Update #TempNSToChuc
SET  NgachHoSinhCaoDang= @count
--NgachHoSinhTrungCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.297')
Update #TempNSToChuc
SET  NgachHoSinhTrungCap= @count
--NgachHoSinhSoCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.298')
Update #TempNSToChuc
SET  NgachHoSinhSoCap= @count
   
--LuongY
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9991' )
Update #TempNSToChuc
SET  LuongY= @count
--LuongY_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9991' AND GioiTinhID = 1540 )
Update #TempNSToChuc
SET  LuongY_Nu= @count
--DuocTa
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9990' )
Update #TempNSToChuc
SET  DuocTa= @count
--DuocTa_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNghe)) = '9990' AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  DuocTa_Nu= @count
--NgachDuocTa
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '16.136')
Update #TempNSToChuc
SET  NgachDuocTa= @count
   
--TDCMSauDaiHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1305)
Update #TempNSToChuc
SET  TDCMSauDaiHoc= @count
--TDCMSauDaiHoc_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1305 AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  TDCMSauDaiHoc_Nu= @count
--TDCMDaiHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1304)
Update #TempNSToChuc
SET  TDCMDaiHoc= @count
--TDCMDaiHoc_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1304 AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  TDCMDaiHoc_Nu= @count
--TDCMCaoDang
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1303)
Update #TempNSToChuc
SET  TDCMCaoDang= @count
--TDCMCaoDang_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1303 AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  TDCMCaoDang_Nu= @count
--TDCMTrungHoc
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1302)
Update #TempNSToChuc
SET  TDCMTrungHoc= @count
--TDCMTrungHoc_Nu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LoaiBangCapID = 1302 AND GioiTinhID = 1540)
Update #TempNSToChuc
SET  TDCMTrungHoc_Nu= @count
   
--NgachCBKhacChuyenVienCaoCap
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '01.001')
Update #TempNSToChuc
SET  NgachCBKhacChuyenVienCaoCap= @count
--NgachCBKhacChuyenVienChinh
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '01.002')
Update #TempNSToChuc
SET  NgachCBKhacChuyenVienChinh= @count
--NgachCBKhacChuyenVien
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '01.003')
Update #TempNSToChuc
SET  NgachCBKhacChuyenVien= @count
--NgachCBKhacCanSu
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE LTRIM(RTRIM(MaNgach)) = '01.004')
Update #TempNSToChuc
SET  NgachCBKhacCanSu= @count
--NgachCBKhacNhanVien--FG
SET @count = (SELECT COUNT(*) FROM #TempNSNhanVien WHERE MaNgach IS NULL)
Update #TempNSToChuc
SET  NgachCBKhacNhanVien= @count

SELECT * FROM #TempNSToChuc
END

GO

--exec [sp_GetNsToChuc] @ToChucID=3,@Debug =1;exec [sp_GetNsToChuc] @ToChucID=4
--exec [sp_GetNsToChuc] @ToChucID=3,@Debug =1

